﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using StoreManagementSystem.DomainObjects;
using System.Text;
using System.Data;

namespace StoreManagementSystem.DataAccessLayer
{
    public class SQLProductDAO : IDataAccessObject<Entity>
    {
        private ISQLDataProvider DataProvider { get; set; }
        public SQLProductDAO(ISQLDataProvider SQLDataProvider) 
        {
            DataProvider = SQLDataProvider;
        }


        public void create(Entity e)
        {
             if (e is Product)
            {
                Product product = e as Product;
                List<SQLParam> dbparams = new List<SQLParam>();
                dbparams.Add(new SQLParam("?id", product.id));
                dbparams.Add(new SQLParam("?Name", product.Name));
                dbparams.Add(new SQLParam("?Price", product.Price));
                dbparams.Add(new SQLParam("?Quantity", product.Quantity));
                dbparams.Add(new SQLParam("?Description", product.Description));
                dbparams.Add(new SQLParam("?Brand", product.Brand.Name));

                // build the parameter string to pass in values
                StringBuilder paramstr = new StringBuilder();
                for (int i = 0; i < dbparams.Count - 1; i++)
                {
                    paramstr.Append(dbparams[i].Name + ",");
                }
                paramstr.Append(dbparams[dbparams.Count - 1].Name);

                string SQL = "INSERT INTO product (id,Name,Price,Quantity,Description,Brand) " +
                        "VALUES(" + paramstr.ToString() + ");";
                DataProvider.Open();
                DataProvider.ExecuteSQL(SQL, dbparams, false);
                DataProvider.Close();
            }
        }
        

        public Entity get(params string[] parameters)
        {
            var factory = new SQLDAOFactory(this.DataProvider);
            var brandDAO = factory.createDAO("brand");


            string Name = parameters[0];
            string Brand = parameters[1];
            List<SQLParam> dbparams = new List<SQLParam>();
            dbparams.Add(new SQLParam("?Name", Name));
            dbparams.Add(new SQLParam("?Brand", Brand));

            string SQL = "SELECT * FROM product WHERE (Name=?Name) AND (Brand=Brand)";
            DataProvider.Open();
            DataTable dt = DataProvider.ExecuteSQL(SQL, dbparams, true);
            DataProvider.Close();
            if (dt.Rows.Count == 0)
                return null;
            Product product = new Product();
            product.id = dt.Rows[0]["id"].ToString();
            product.Name = dt.Rows[0]["Name"].ToString();
            product.Brand = brandDAO.get(dt.Rows[0]["Brand"].ToString()) as Brand;
            product.Description = dt.Rows[0]["Description"].ToString();
            product.Price = (int) dt.Rows[0]["Price"];
            product.Quantity = (int) dt.Rows[0]["Quantity"];
            return product;
        }

        public void remove(Entity e)
        {
            throw new NotImplementedException();
        }

        public void update(Entity e)
        {
            throw new NotImplementedException();
        }
    }
}